A Make2D-DB II DATABASE: version 2.50, built 18-Jul-2006
Dumped on 2006-07-18
To do: generate and send automatically a unique - hidden - database identifier from ExPASy to the remote database, then LOCK table. Make also this identifier queriable from the remote main interface.
| F-Key | Name | Type | Description |
|---|---|---|---|
| databaseidentifier | character varying(32) | NOT NULL | |
| databasename | character varying(128) | NOT NULL | |
| databasedescription | text | ||
| databaserelease | integer | NOT NULL DEFAULT make2db_last_release() | |
| databasesubrelease | smallint | ||
| databasereleasedate | date | NOT NULL DEFAULT ('now'::text)::date | |
| databasereleasenotes | text | ||
| databasemainsummary | text | ||
| databaseinterfaceuri | text | ||
| databaseinterfaceurinumber | smallint | DEFAULT 1 | |
| copyright | text | ||
| contact | text |
| User | |||||||
|---|---|---|---|---|---|---|---|
| mostaguir | |||||||
| select2d |
FUNCTION TO RETURN THE LENGTH OF THE N-DIMENSION OF AN ARRAY Needed with postgreSQL prior to 7.4, otherwise use array_upper (array_lower) Call function by 2 arguments, and use void {} for the others (there is a postgreSQL problem casting _TYPES[]) ex: array_dim_length(CAST('{}' AS TEXT[]), CAST(my_mappingTechniques AS VARCHAR[]), CAST('{}' AS INTEGER[]), CAST('{}' AS FLOAT[]), 1); to evaluate the first dimension of an INT array!! (prior to potsgres 7.4)
-- FUNCTION TO RETURN THE LENGTH OF THE N-DIMENSION OF AN ARRAY
---------------------------------------------------------------
-- Needed with postgreSQL prior to 7.4, otherwise use array_upper (array_lower)
-- Call function by 2 arguments, and use void {} for the others (there is a postgreSQL problem casting _TYPES[])
-- ex: array_dim_length(CAST('{}' AS TEXT[]), CAST(my_mappingTechniques AS VARCHAR[]), CAST('{}' AS INTEGER[]), CAST('{}' AS FLOAT[]), 1);
-- to evaluate the first dimension of an INT array!! (prior to potsgres 7.4)
DECLARE
my_mappingTechniques_text ALIAS for $1;
my_mappingTechniques_varchar ALIAS for $2;
my_mappingTechniques_int ALIAS for $3;
my_mappingTechniques_float ALIAS for $4;
my_dimension ALIAS for $5;
my_ar_length INT2;
my_ar_length_string TEXT;
my_pos1 INT2;
my_pos2 INT2;
BEGIN
-- Since postgreSQL 7.4: use Numerical array_upper and array_lower rather then array_dims
IF array_dims(my_mappingTechniques_text) IS NOT NULL THEN my_ar_length_string := array_dims(my_mappingTechniques_text);
ELSE IF my_mappingTechniques_varchar IS NOT NULL THEN my_ar_length_string := array_dims(my_mappingTechniques_varchar);
ELSE IF my_mappingTechniques_int IS NOT NULL THEN my_ar_length_string := array_dims(my_mappingTechniques_int);
ELSE IF my_mappingTechniques_float IS NOT NULL THEN my_ar_length_string := array_dims(my_mappingTechniques_float);
ELSE RETURN NULL;
END IF; END IF; END IF; END IF;
IF my_ar_length_string IS NULL THEN RETURN NULL;
END IF;
IF my_dimension > 1 THEN
FOR ii IN 1..(my_dimension-1)
LOOP
my_pos1 := strpos(my_ar_length_string,']');
my_ar_length_string := substr(my_ar_length_string, my_pos1 + 1);
END LOOP;
END IF;
my_pos1 := strpos(my_ar_length_string,':');
my_pos2 := strpos(my_ar_length_string,']');
my_ar_length_string := substr(my_ar_length_string, my_pos1 +1, my_pos2 - my_pos1 -1);
IF my_ar_length_string !~ '^[0-9]+$' THEN RETURN NULL;
END IF;
my_ar_length := my_ar_length_string::INT2;
RETURN my_ar_length;
END;
FUNCTION TO CONVERT d MONTHS in MMM FORMAT
-- FUNCTION TO CONVERT d MONTHS in MMM FORMAT
----------------------------------------------
DECLARE
month_number ALIAS for $1;
month CHAR(3);
BEGIN
IF month_number = 1 THEN RETURN 'JAN'; ELSE IF month_number = 2 THEN RETURN 'FEB';
ELSE IF month_number = 3 THEN RETURN 'MAR'; ELSE IF month_number = 4 THEN RETURN 'APR';
ELSE IF month_number = 5 THEN RETURN 'MAY'; ELSE IF month_number = 6 THEN RETURN 'JUN';
ELSE IF month_number = 7 THEN RETURN 'JUL'; ELSE IF month_number = 8 THEN RETURN 'AUG';
ELSE IF month_number = 9 THEN RETURN 'SEP'; ELSE IF month_number = 10 THEN RETURN 'OCT';
ELSE IF month_number = 11 THEN RETURN 'NOV'; ELSE IF month_number = 12 THEN RETURN 'DEC';
ELSE RETURN 'XXX';
END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF; END IF;
END;
FUNCTION TO CONVERT HEXADECIMAL TO INTEGER
-- FUNCTION TO CONVERT HEXADECIMAL TO INTEGER
---------------------------------------------
DECLARE
my_hex ALIAS for $1;
my_hex_str VARCHAR(64);
my_digit TEXT;
my_int INT8;
ii INT8;
BEGIN
my_hex_str = my_hex;
my_int := 0;
ii := 1;
WHILE length(my_hex_str) > 0
LOOP
my_digit := substr(my_hex_str, length(my_hex_str));
IF my_digit = 'A' THEN my_digit := '10';
ELSE IF my_digit = 'B' THEN my_digit := '11';
ELSE IF my_digit = 'C' THEN my_digit := '12';
ELSE IF my_digit = 'D' THEN my_digit := '13';
ELSE IF my_digit = 'E' THEN my_digit := '14';
ELSE IF my_digit = 'F' THEN my_digit := '15';
END IF; END IF; END IF; END IF; END IF; END IF;
my_hex_str := substr(my_hex_str, 1, length(my_hex_str) - 1);
my_int := my_int + (my_digit::INT4) * ii;
ii := ii * 16;
END LOOP;
RETURN my_int;
END;
FUNCTION TO CONVERT INTEGER TO HEXADECIMAL
-- FUNCTION TO CONVERT INTEGER TO HEXADECIMAL
---------------------------------------------
DECLARE
my_int ALIAS for $1;
my_int_num INT8;
my_digit INT2;
my_car CHAR(1);
my_hex VARCHAR(64);
ii INT2;
BEGIN
my_int_num = my_int::TEXT;
my_hex := '';
ii := 16;
WHILE my_int_num > 0
LOOP
my_digit := mod(my_int_num, ii);
my_int_num := (my_int_num::FLOAT - my_digit::FLOAT) / ii;
IF my_digit = 10 THEN my_car := 'A';
ELSE IF my_digit = 11 THEN my_car := 'B';
ELSE IF my_digit = 12 THEN my_car := 'C';
ELSE IF my_digit = 13 THEN my_car := 'D';
ELSE IF my_digit = 14 THEN my_car := 'E';
ELSE IF my_digit = 15 THEN my_car := 'F';
ELSE my_car := my_digit::TEXT;
END IF; END IF; END IF; END IF; END IF; END IF;
my_hex := my_car::VARCHAR || my_hex;
END LOOP;
RETURN my_hex;
END;
FUNCTION TO FORMAT A VIEW (tmp) ENTRY FOR ASCII OUTPUT. Call function by 3 arguments: (AC, line length, hide private annotations), (output line length is formatted by make2db_format_entry(output, line length, hide private annotations))
-- FUNCTION TO FORMAT A VIEW (tmp) ENTRY FOR ASCII OUTPUT
---------------------------------------------------------
-- Call function by 3 arguments: (AC, line length, hide private annotations)
--(output line length is formatted by make2db_format_entry(output, line length, hide private annotations))
DECLARE
my_ac VARCHAR(32);
my_entry TEXT;
my_record RECORD;
my_line_length INT;
my_hide_private TEXT;
my_month TEXT;
my_month_converted TEXT;
my_ac_first RECORD;
BEGIN
SET DATESTYLE TO 'POSTGRES, EUROPEAN';
my_ac := $1;
IF my_ac = '' THEN
RETURN 'No entry was given. Please, try again.
';
END IF;
my_line_length := $2;
IF my_line_length <25 THEN
my_line_length = 75;
END IF;
my_hide_private := $3;
SELECT INTO my_ac_first common.make2db_primary_accession(my_ac) AS AC;
IF NOT FOUND THEN
RETURN 'There is currently no entry ' || my_ac || '. Please, try again.
';
ELSE
SELECT INTO my_record * FROM ViewEntry WHERE accession_number::VARCHAR(32) = my_ac_first.AC;
END IF;
my_entry := 'ID ' || my_record.identifier || ';';
IF (length(my_record.id_method) > 0) THEN
my_entry := my_entry || ' ' || my_record.id_method || '.';
END IF;
my_entry := my_entry || '
AC ' || my_record.accession_number || ';';
IF my_record.secondary_identifiers <> '--' AND my_record.secondary_identifiers <> '' THEN
my_entry := my_entry || ' ' || my_record.secondary_identifiers || ';';
END IF;
IF my_record.creation ~ ' [0-9]D' THEN -- escaped parenthesis with[0-9]) cause a BUG in postgres 7.4!!
my_record.creation := substr(my_record.creation, 1, 17) || '0' || substr(my_record.creation, 18);
END IF;
my_month := substr(my_record.creation, 4, 2);
my_month_converted := '-' || get_month(my_month::INT) || '-';
my_month := '-' || my_month || '-';
my_record.creation = substitute_text(my_record.creation, my_month, my_month_converted);
/*my_month = get_month(my_month::INT);
my_record.creation = substr(my_record.creation, 1, 3) || my_month || substr(my_record.creation, 6, length(my_record.creation) - 6) || ', Created)';*/
my_entry := my_entry || '
DT ' || my_record.creation || '.';
my_month := substr(my_record.version_2d, 4, 2);
my_month_converted := '-' || get_month(my_month::INT) || '-';
my_month := '-' || my_month || '-';
my_record.version_2d = substitute_text(my_record.version_2d, my_month, my_month_converted);
my_entry := my_entry || '
DT ' || my_record.version_2d || '.';
my_month := substr(my_record.version_general, 4, 2);
my_month_converted := '-' || get_month(my_month::INT) || '-';
my_month := '-' || my_month || '-';
my_record.version_general = substitute_text(my_record.version_general, my_month, my_month_converted);
my_entry := my_entry || '
DT ' || my_record.version_general || '.';
IF my_record.description <> '--' AND my_record.description <> '' THEN
my_entry := my_entry || '
DE ' || my_record.description || '.';
END IF;
IF my_record.genes <> '--' AND my_record.genes <> '' THEN
my_entry := my_entry || '
GN ' || substitute_text(my_record.genes, ' and ','
GN and
GN ') || ';';
END IF;
my_entry := my_entry || '
OS ' || my_record.organism || '.
OC ' || my_record.organism_classification || '.';
IF my_record.taxonomy_cross_reference <> '--' AND my_record.taxonomy_cross_reference <> '' THEN
my_entry := my_entry || '
OX ' || my_record.taxonomy_cross_reference || ';';
END IF;
IF my_record.masters <> '--' AND my_record.masters <> '' THEN
my_entry := my_entry || '
MT ' || my_record.masters || '.';
END IF;
my_entry := my_entry || '
IM ' || my_record.images || '.';
my_entry := my_entry || '
' || my_record.reference_lines;
IF my_record.free_comments <> '--' AND my_record.free_comments <> '' THEN
my_entry := my_entry || '
CC ' || substitute_text(my_record.free_comments,'
','
CC ');
END IF;
IF my_record.one_d_comments <> '--' AND my_record.one_d_comments <> '' THEN
my_entry := my_entry || '
1D ' || substitute_text(my_record.one_d_comments,'
','
1D ');
END IF;
IF my_record.one_d_blocks <> '--' AND my_record.one_d_blocks <> '' THEN
my_entry := my_entry || '
1D ' || substitute_text(my_record.one_d_blocks,'
','
1D ');
END IF;
IF my_record.two_d_comments <> '--' AND my_record.two_d_comments <> '' THEN
my_entry := my_entry || '
2D ' || substitute_text(my_record.two_d_comments,'
','
2D ');
END IF;
IF my_record.two_d_blocks <> '--' AND my_record.two_d_blocks <> '' THEN
my_entry := my_entry || '
2D ' || substitute_text(my_record.two_d_blocks,'
','
2D ');
END IF;
IF my_record.database_cross_reference <>'--' AND my_record.database_cross_reference <> '' THEN
my_record.database_cross_reference := substitute_text(my_record.database_cross_reference,'
','
DR ');
my_entry := my_entry || '
DR ' || my_record.database_cross_reference;
END IF;
my_entry := make2db_format_entry(my_entry, my_line_length, my_hide_private);
my_entry := my_entry || '//';
RETURN my_entry;
END;
FUNCTION TO GET A STRING OF ALL MAPPING METHODS FOR A SPECIFIC ENTRY SPOT
-- FUNCTION TO GET A STRING OF ALL MAPPING METHODS FOR A SPECIFIC ENTRY SPOT
----------------------------------------------------------------------------
DECLARE
my_ac VARCHAR(32);
my_spotID VARCHAR(16);
my_gelID INT;
my_record RECORD;
my_mapping_method_string TEXT;
BEGIN
my_ac := $1;
IF my_ac = '' THEN
RETURN '';
END IF;
my_spotID := $2;
IF my_spotID = '' THEN
RETURN '';
END IF;
my_gelID := $3;
IF my_gelID < 1 THEN
RETURN '';
END IF;
my_mapping_method_string := '';
FOR my_record IN -- grouped Mapping Methods, possibly over several lines
SELECT '{' || array_to_string(mappingtechnique,',') || '} ' AS mappingTechniqueString
FROM spotentrymappingtopic
WHERE ac = my_ac AND spotID = my_spotID AND gelID = my_gelID AND mappingTechnique IS NOT NULL ORDER BY 1
LOOP
my_mapping_method_string := my_mapping_method_string || my_record.mappingTechniqueString;
END LOOP;
IF (length(my_mapping_method_string) > 0) THEN
my_mapping_method_string := substr(my_mapping_method_string, 0, length(my_mapping_method_string));
END IF;
return my_mapping_method_string;
END;
FUNCTION TO FORMAT ENTRIES TO X CHARACTERS PER LINE. Call function by 3 arguments: (full text, line length, hide private annotations)
-- FUNCTION TO FORMAT ENTRIES TO X CHARACTERS PER LINE
------------------------------------------------------
-- Call function by 3 arguments: (full text, line length, hide private annotations)
DECLARE
my_full_entry TEXT;
my_new_entry TEXT;
my_old_line TEXT;
my_old_line_copy TEXT;
my_assembled_part TEXT;
my_keyword VARCHAR(11);
my_line_length INT2;
my_spaces TEXT;
my_hide_private TEXT;
ii INT2;
jj INT2;
BEGIN
my_full_entry := $1;
my_line_length := $2;
my_hide_private := $3;
my_new_entry := '';
my_spaces := ' ';
IF substr(my_full_entry,length(my_full_entry)) <> '
' THEN
my_full_entry := my_full_entry || '
';
END IF;
IF my_line_length < 25 THEN
my_line_length = 75;
END IF;
-- substract the 5 caracters for the keyword and the spaces, add one to include the
my_line_length := my_line_length - 4;
ii := 0;
WHILE strpos(my_full_entry, '
') <> 0 AND ii < 999
LOOP
ii := ii +1;
my_old_line := substr(my_full_entry, 1, strpos(my_full_entry, '
'));
my_full_entry := substr(my_full_entry, strpos(my_full_entry, '
') +1);
my_old_line := substr(my_old_line, 1, length(my_old_line) - 1);
my_old_line_copy := my_old_line;
my_assembled_part := '';
IF (length(my_hide_private) > 0) AND (my_old_line_copy ~ my_hide_private)
THEN
my_old_line := ''; my_old_line_copy := '';
ELSE
IF substr(my_old_line, 3, 3) <> ' ' THEN
RETURN $1;
END IF;
-- remove any head spaces --
WHILE substr(my_old_line_copy,6,1) = ' '
LOOP my_old_line_copy = substr(my_old_line, 1, 5) || substr(my_old_line_copy, 7);
END LOOP;
my_keyword := substr(my_old_line, 1, 5);
my_old_line_copy := substr(my_old_line_copy, 6);
IF strpos(my_old_line_copy,' ') = 0 OR length(my_old_line_copy) <= my_line_length THEN
my_new_entry := trim(my_new_entry) || my_keyword || my_old_line_copy || '
';
ELSE
jj := 0;
WHILE strpos(my_old_line_copy, ' ') <> 0 AND jj < 999
LOOP
IF length(my_assembled_part) + (strpos(my_old_line_copy, ' ')) > my_line_length
AND my_assembled_part <> ''
THEN
my_new_entry := trim(my_new_entry || my_keyword || my_assembled_part) || '
';
my_spaces := ' ';
IF my_assembled_part ~* '^-!- [a-z]' THEN my_spaces = ' ';
END IF;
IF (my_keyword ~* '^[1-9]D' OR (my_keyword~* '^CC'))
AND length(my_keyword) = 5 THEN
IF my_keyword~* '^CC' THEN my_spaces := my_spaces || ' ';
END IF;
my_keyword := my_keyword || my_spaces;
END IF;
my_assembled_part := '';
ELSE
my_assembled_part := my_assembled_part ||
substr(my_old_line_copy, 1, strpos(my_old_line_copy, ' '));
my_old_line_copy := substr(my_old_line_copy, strpos(my_old_line_copy, ' ') + 1);
END IF;
END LOOP;
my_new_entry := trim(my_new_entry || my_keyword || my_assembled_part);
IF length(my_old_line_copy) > 0 THEN
my_new_entry := my_new_entry || ' ' || my_old_line_copy || '
';
END IF;
END IF;
END IF;
IF substr(my_new_entry, length(my_new_entry), 1) <> '
' THEN
my_new_entry := my_new_entry || '
';
END IF;
END LOOP;
RETURN my_new_entry;
END;
FUNCTION TO GET LAST GENERAL RELEASE NUMBER
-- FUNCTION TO GET LAST GENERAL RELEASE NUMBER
----------------------------------------------
BEGIN
RETURN max(Release.releaseNum) FROM Release;
END;
FUNCTION TO GET THE PRIMARY ACCESSION NUMBER
-- FUNCTION TO GET THE PRIMARY ACCESSION NUMBER
-----------------------------------------------
DECLARE
ac_arg_init ALIAS for $1;
ac_arg VARCHAR(32);
my_record RECORD;
BEGIN
ac_arg = upper(ac_arg_init);
SELECT INTO my_record Entry.AC FROM Entry WHERE upper(Entry.AC) = ac_arg::NAME;
IF my_record.AC IS NOT NULL THEN RETURN ac_arg; END IF;
SELECT INTO my_record Entry.AC, Entry.ID FROM Entry WHERE upper(Entry.ID) = ac_arg::NAME;
IF my_record.AC IS NOT NULL THEN RETURN my_record.AC; END IF;
-- with SP entry spliting we may have several AC for the same econdary AC (return a list?)
SELECT INTO my_record SecondaryAC.AC FROM SecondaryAC WHERE upper(SecondaryAC.secondaryAC) = ac_arg::NAME LIMIT 1;
IF my_record.AC IS NOT NULL THEN RETURN my_record.AC; END IF;
RETURN NULL;
END;
FUNCTION TO RETURN THE DATABASE RELEASE (SUB-RELEASE) FOR A GIVEN DATE
-- FUNCTION TO RETURN THE DATABASE RELEASE (and SUB-RELEASE) OF A GIVEN DATE
----------------------------------------------------------------------------
-- Call function by 3 arguments:
-- (DATE, TRUE for rather the next version or FALSE for rather the previous version, TRUE to include sub-release)
-- Returns Real format: version.subVersion (Where subVersion is [dd] - 0 filled - if subVersion >0 and <100)
DECLARE
my_date ALIAS for $1;
my_after ALIAS for $2;
my_return_data ALIAS for $3;
my_record RECORD;
my_pre_subRelease TEXT;
my_old_text TEXT;
my_new_text TEXT;
BEGIN
IF my_after IS FALSE THEN
SELECT INTO my_record releaseNum, subRelease FROM Release WHERE releaseDate <= my_date
ORDER BY (releaseNum *10000 + subRelease) DESC LIMIT 1;
ELSE
SELECT INTO my_record releaseNum, subRelease FROM Release WHERE releaseDate >= my_date
ORDER BY (releaseNum *10000 + subRelease) LIMIT 1;
END IF;
IF my_return_data IS FALSE THEN
RETURN my_record.releaseNum::REAL;
END IF;
IF NOT FOUND THEN
IF my_after IS FALSE THEN
SELECT INTO my_record min(releaseNum) as releaseNum, min(subRelease) as subRelease
FROM Release WHERE releaseNum = (SELECT min(releaseNum) FROM Release);
ELSE
SELECT INTO my_record max(releaseNum) as releaseNum, max(subRelease) as subRelease
FROM Release WHERE releaseNum = (SELECT max(releaseNum) FROM Release);
END IF;
END IF;
my_pre_subRelease := '';
IF (my_record.subRelease > 0 AND my_record.subRelease < 10) THEN
my_pre_subRelease := '0';
END IF;
RETURN (my_record.releaseNum || '.' || my_pre_subRelease || my_record.subRelease)::REAL;
END;
FUNCTION TO RETRIEVE AND CONCATENATE A LIST OF ENTRY FIELDS
-- FUNCTION TO RETRIEVE AND CONCATENATE A LIST OF ENTRY FIELDS
--------------------------------------------------------------
DECLARE
my_AC ALIAS for $1;
my_line ALIAS for $2;
my_reunited TEXT;
my_record RECORD;
my_record2 RECORD;
ii INT2;
my_temp1_txt TEXT;
my_temp2_txt TEXT;
my_temp2_txt_aa TEXT;
my_temp2_txt_pmf TEXT;
my_temp2_txt_msms TEXT;
my_temp2_txt_pepseq TEXT;
my_temp3_txt TEXT;
my_temp4_txt TEXT;
my_temp5_txt TEXT;
my_spot_nature TEXT;
my_maps_record RECORD;
my_last_master VARCHAR(32); -- used in 2D blocks
my_last_2d_method TEXT;
my_substituted_text TEXT;
BEGIN
IF my_line = 'AC' THEN
/************************/
FOR my_record IN
SELECT secondaryAC
FROM SecondaryAC
WHERE CAST(AC AS varchar(32)) = my_AC
ORDER BY 1
LOOP
IF my_reunited IS NULL
THEN
my_reunited := CAST(my_record.secondaryAC AS varchar(20));
ELSE
my_reunited := my_reunited || '; ' || CAST(my_record.secondaryAC AS varchar(50));
END IF;
--RAISE NOTICE 'stored: %', my_reunited;
END LOOP;
ELSE IF my_line = 'DE' THEN
/*****************************/
SELECT INTO my_record description FROM Entry WHERE CAST(Entry.AC AS varchar(32)) = my_AC;
my_reunited := my_record.description;
SELECT INTO my_record enzymeCode FROM EnzymeNomenclature WHERE CAST(EnzymeNomenclature.AC AS varchar(32)) = my_AC;
IF my_record.enzymeCode IS NOT NULL AND my_reunited !~ my_record.enzymeCode
THEN
my_reunited := my_reunited || ' (updated EC ' || my_record.enzymeCode || ')';
END IF;
ELSE IF my_line = 'IM' THEN
/*****************************/
FOR my_record IN
SELECT Gel.shortName
FROM Gel, EntryGelImage
WHERE CAST(EntryGelImage.AC AS varchar(32)) = my_AC AND EntryGelImage.gelID = Gel.gelID
AND Gel.showFlagSwitch IS TRUE AND EntryGelImage.showFlagSwitch IS TRUE
ORDER BY 1
LOOP
IF my_reunited IS NULL
THEN
my_reunited := CAST(my_record.shortName AS varchar(50));
ELSE
my_reunited := my_reunited || ', ' || CAST(my_record.shortName AS varchar(50));
END IF;
--RAISE NOTICE '%', my_record.shortName;
--RAISE NOTICE 'stored: %', my_reunited;
END LOOP;
ELSE IF my_line = 'MT' THEN
/*****************************/
FOR my_record IN
SELECT Gel.shortName
FROM Gel, EntryGelMasTer
WHERE CAST(EntryGelMasTer.AC AS varchar(32)) = my_AC AND EntryGelMasTer.gelID = Gel.gelID
AND Gel.showFlagSwitch IS TRUE AND EntryGelMaster.showFlagSwitch IS TRUE
ORDER BY 1
LOOP
IF my_reunited IS NULL
THEN
my_reunited := CAST(my_record.shortName AS varchar(50));
ELSE
my_reunited := my_reunited || ', ' || CAST(my_record.shortName AS varchar(50));
END IF;
END LOOP;
ELSE IF my_line = 'CC' THEN
/*****************************/
FOR my_record IN
SELECT CommentTopic.CommentTopicName, CommentEntryFreeText.commentFreeText
FROM CommentTopic, CommentEntryFreeText
WHERE CAST(CommentEntryFreeText.AC AS varchar(32)) = my_AC
AND CommentTopic.commentTopicID = CommentEntryFreeText.commentTopicID
ORDER BY 1,2
LOOP
IF my_reunited IS NULL
THEN
my_reunited := '-!- ' || CAST(my_record.commentTopicName AS varchar(64)) || ': ' || my_record.commentFreeText;
ELSE
my_reunited := my_reunited || '
-!- ' || CAST(my_record.commentTopicName AS varchar(64))
|| ': ' || my_record.commentFreeText;
END IF;
END LOOP;
ELSE IF my_line = '1C' OR my_line = '2C' THEN
/*************************************************/
IF my_line = '1C' THEN ii =1;
ELSE IF my_line = '2C' THEN ii =2;
END IF;
END IF;
FOR my_record IN
SELECT CommentTopic.commentTopicName, CommentEntry2D.commentFreeText
FROM CommentTopic, CommentEntry2D
WHERE CAST(CommentEntry2D.AC AS varchar(32)) = my_AC
AND CommentTopic.commentTopicID = CommentEntry2D.commentTopicID AND CommentEntry2D.gelDimension = ii
ORDER BY 1,2
LOOP
IF my_reunited IS NULL
THEN
my_reunited := '-!- ' || CAST(my_record.commentTopicName AS varchar(64)) || ': ' || my_record.commentFreeText;
ELSE
my_reunited := my_reunited || '
-!- ' || CAST(my_record.commentTopicName AS varchar(64))
|| ': ' || my_record.commentFreeText;
END IF;
END LOOP;
ELSE IF (my_line = '2D' OR my_line ='1D') THEN
/**************************************************/
IF EXISTS (SELECT tablename FROM pg_tables WHERE lower(tablename) = 'buffer_make2db_reunit_2d_topics')
THEN
DELETE FROM buffer_make2db_reunit_2d_topics; -- No need to lock table (serializable level is set on)
ELSE
CREATE TEMPORARY TABLE buffer_make2db_reunit_2d_topics ( stockSpot VARCHAR(16), stockMe1 TEXT, stockMe2 TEXT, mapping BOOLEAN );
END IF;
my_reunited := '';
my_last_master := '';
FOR my_maps_record IN SELECT Gel.shortName, Gel.gelID FROM Gel, EntryGelImage
WHERE CAST(EntryGelImage.AC AS varchar(32)) = my_AC AND EntryGelImage.gelID = Gel.gelID
AND Gel.showFlagSwitch IS TRUE AND EntryGelImage.showFlagSwitch IS TRUE
ORDER BY 1
LOOP /* loop over each master */
my_temp1_txt := '';
my_temp2_txt := '';
my_temp2_txt_aa := '';
my_temp2_txt_pmf := '';
my_temp2_txt_msms := '';
my_temp2_txt_pepseq := '';
my_temp3_txt := '';
my_temp4_txt := '';
my_temp5_txt := '';
FOR my_record IN
-- -- MASS SPECTROMETRY data is not showed within the entry itself
SELECT Gel.shortName, ViewSpotEntry.spotID, ViewSpotEntry.gelID, ViewSpotEntry.AC,
ViewSpotEntry.fragment, ViewSpotEntry.AminoAcidList, ViewSpotEntry.aa_version,
ViewSpotEntry.peptideSequences, ViewSpotEntry.peptseq_version,
ViewSpotEntry.peptideMasses, ViewSpotEntry.pmf_version,
ViewSpotEntry.msms, ViewSpotEntry.ms_version,
Spot.mw, Spot.pI
FROM Gel, ViewSpotEntry, Spot
WHERE Gel.shortName = my_maps_record.shortName
AND Gel.gelID = Spot.gelID
AND CAST(ViewSpotEntry.AC AS varchar(32)) = my_AC
AND ViewSpotEntry.spotID = Spot.spotID
AND ViewSpotEntry.gelID = Spot.gelID
ORDER BY ViewSpotEntry.spotID
LOOP /* lines of current master */
IF (my_record.shortName = ''
OR (my_line = '1D' AND my_record.pI IS NOT NULL)
OR (my_line = '2D' AND my_record.pI IS NULL))
THEN EXIT;
END IF;
IF my_last_master <> my_maps_record.shortName THEN
my_reunited := my_reunited || my_temp2_txt;
my_temp1_txt := '';
my_temp2_txt := '';
my_temp2_txt_aa := '';
my_temp2_txt_pmf := '';
my_temp2_txt_msms := '';
my_temp2_txt_pepseq := '';
my_temp3_txt := '';
my_reunited := my_reunited || '
-!- MASTER: ' || my_record.shortName || ';';
my_last_master := my_record.shortName;
END IF;
IF my_line = '2D' THEN
IF trunc(my_record.pI) < 10 THEN my_temp1_txt := substr(to_char(my_record.pI, '0d00'),2);
ELSE my_temp1_txt := substr(to_char(my_record.pI, '00d00'),2);
END IF;
my_spot_nature := 'SPOT';
my_reunited := my_reunited || '
-!- PI/MW: SPOT ' || my_record.spotID || '=';
my_reunited := my_reunited || my_temp1_txt || '/';
my_reunited := my_reunited || CAST(my_record.mw AS varchar(8)) || ';';
ELSE
my_spot_nature := 'BAND';
my_reunited := my_reunited || '
-!- MW: BAND ' || my_record.spotID || '=';
my_reunited := my_reunited || CAST(my_record.mw AS varchar(8)) || ';';
END IF;
IF my_record.fragment = 'true' THEN my_reunited := my_reunited || ' !FRAGMENT!';
END IF;
/* Identification Methods */
-- a line-feed is the separator for several data sets
-- MASS SPECTROMETRY data is not showed within the entry itself
IF my_record.AminoAcidList IS NOT NULL THEN
my_record.AminoAcidList := '
' || my_record.AminoAcidList;
my_substituted_text = ';
-!- AMINO ACID COMPOSITION: ' || my_spot_nature || ' ' || my_record.spotID || ': ';
my_record.AminoAcidList := common.substitute_text(my_record.AminoAcidList,'
', my_substituted_text);
my_temp2_txt_aa := my_temp2_txt_aa || substr(my_record.AminoAcidList,2) || ';';
END IF;
IF my_record.peptideMasses IS NOT NULL THEN
IF (strpos(my_record.peptideMasses,' [Documents] ') >0) THEN
-- when [Documents] was at the end of the field:
-- my_record.peptideMasses := substr(my_record.peptideMasses, 1, (strpos(my_record.peptideMasses,' [Documents] ')-1));
-- now, it is on its beginning, on a separate line:
my_record.peptideMasses := substr(my_record.peptideMasses, (strpos(my_record.peptideMasses,'
')+1));
IF (length(my_record.peptideMasses) < 1) THEN -- only documents
my_record.peptideMasses := '0';
END IF;
END IF;
my_record.peptideMasses := '
' || my_record.peptideMasses;
my_substituted_text = '.
-!- PEPTIDE MASSES: ' || my_spot_nature || ' ' || my_record.spotID || ': ';
my_record.peptideMasses := common.substitute_text(my_record.peptideMasses,'
', my_substituted_text);
my_temp2_txt_pmf := my_temp2_txt_pmf || substr(my_record.peptideMasses,2) || '.';
END IF;
IF my_record.msms IS NOT NULL THEN
IF (strpos(my_record.msms,' [Documents] ') >0) THEN
-- my_record.msms := substr(my_record.msms, 1, (strpos(my_record.msms,' [Documents] ')-1));
my_record.msms := substr(my_record.msms, (strpos(my_record.msms,'
')+1));
IF (length(my_record.msms) < 1) THEN -- only documents
my_record.msms := '[0:0]';
END IF;
END IF;
my_record.msms := '
' || my_record.msms;
my_substituted_text = '
-!- TANDEM MASS SPECTROMETRY: ' || my_spot_nature || ' ' || my_record.spotID || ': ';
my_record.msms := common.substitute_text(my_record.msms,'
', my_substituted_text);
my_temp2_txt_msms := my_temp2_txt_msms || substr(my_record.msms,1);
END IF;
IF my_record.peptideSequences IS NOT NULL THEN
my_record.peptideSequences := '
' || my_record.peptideSequences;
my_substituted_text = '.
-!- PEPTIDE SEQUENCES: ' || my_spot_nature || ' ' || my_record.spotID || ': ';
my_record.peptideSequences := common.substitute_text(my_record.peptideSequences,'
', my_substituted_text);
my_temp2_txt_pepseq := my_temp2_txt_pepseq || substr(my_record.peptideSequences,2) || '.';
END IF;
/* stock topics of current spot */
IF EXISTS ( SELECT * FROM SpotEntryGeneralTopic WHERE spotID = my_record.spotID AND gelID = my_record.gelID
AND CAST(AC AS VARCHAR(32)) = my_AC LIMIT 1)
THEN
INSERT INTO buffer_make2db_reunit_2d_topics (stockSpot, stockMe1, stockMe2, mapping)
SELECT my_record.spotID::VARCHAR(16), GeneralTopicDefinition.topicName::TEXT, GeneralTopicEntryData.topicDataText::TEXT, FALSE
FROM SpotEntryGeneralTopic, GeneralTopicEntryData, GeneralTopicDefinition
WHERE SpotEntryGeneralTopic.spotID = my_record.spotID
AND SpotEntryGeneralTopic.gelID = my_record.gelID
AND CAST(SpotEntryGeneralTopic.AC AS VARCHAR(32)) = my_AC
AND GeneralTopicEntryData.topicDataID = SpotEntryGeneralTopic.topicDataID
AND GeneralTopicEntryData.generalTopicID = GeneralTopicDefinition.generalTopicID
ORDER BY GeneralTopicDefinition.topicName, GeneralTopicEntryData.topicDataText;
-- we will not use regular expressions over my_temp4.txt as the fields may contain special characters (no function to escape them in PL/pgsql)
END IF;
/* stock mapping methods of current spot */
IF EXISTS ( SELECT * FROM SpotEntryMappingTopic WHERE spotID = my_record.spotID AND gelID = my_record.gelID
AND CAST(AC AS VARCHAR(32)) = my_AC LIMIT 1)
THEN
INSERT INTO buffer_make2db_reunit_2d_topics (stockSpot, stockMe1, stockMe2, mapping)
SELECT my_record.spotID::VARCHAR(16), GeneralTopicDefinition.topicName::TEXT, GeneralTopicEntryData.topicDataText::TEXT, TRUE
FROM SpotEntryMappingTopic, GeneralTopicEntryData, GeneralTopicDefinition
WHERE SpotEntryMappingTopic.spotID = my_record.spotID
AND SpotEntryMappingTopic.gelID = my_record.gelID
AND CAST(SpotEntryMappingTopic.AC AS VARCHAR(32)) = my_AC
AND GeneralTopicEntryData.topicDataID = SpotEntryMappingTopic.topicDataID
AND GeneralTopicEntryData.generalTopicID = GeneralTopicDefinition.generalTopicID
ORDER BY GeneralTopicDefinition.topicName, GeneralTopicEntryData.topicDataText;
-- we will not use regular expressions over my_temp5.txt as the fields may contain special characters (no
END IF;
END LOOP; /* end loop over lines of current master */
my_temp2_txt := my_temp2_txt_aa || my_temp2_txt_pmf || my_temp2_txt_msms || my_temp2_txt_pepseq;
/* write topics of current spot */
my_last_2d_method := '';
FOR my_record2 IN
SELECT DISTINCT stockMe1, stockMe2 FROM buffer_make2db_reunit_2d_topics WHERE mapping IS FALSE
ORDER BY stockMe1, stockMe2
LOOP
IF (my_record2.stockMe1 = my_last_2d_method) THEN
my_temp4_txt := substr(my_temp4_txt, 0, length(my_temp4_txt));
my_temp4_txt := my_temp4_txt || '; ' || my_record2.stockMe2 || '.';
ELSE
my_temp4_txt := my_temp4_txt || '
-!- ' || my_record2.stockMe1 || ': ' || my_record2.stockMe2 || '.';
END IF;
my_last_2d_method := my_record2.stockMe1;
END LOOP;
/* write mapping methods of current spot */
my_last_2d_method := '';
FOR my_record2 IN
SELECT DISTINCT stockMe1, stockMe2 FROM buffer_make2db_reunit_2d_topics WHERE mapping IS TRUE
ORDER BY stockMe1, stockMe2
LOOP
IF (my_record2.stockMe1 = my_last_2d_method) THEN
my_temp5_txt := substr(my_temp5_txt, 0, length(my_temp5_txt));
my_temp5_txt := my_temp5_txt || '; ' || my_record2.stockMe2 || '.';
ELSE
my_temp5_txt := my_temp5_txt || '
-!- ' || my_record2.stockMe1 || ': ' || my_record2.stockMe2 || '.';
END IF;
my_last_2d_method := my_record2.stockMe1;
END LOOP;
DELETE FROM buffer_make2db_reunit_2d_topics;
my_reunited := my_reunited || my_temp2_txt || my_temp3_txt || my_temp4_txt || my_temp5_txt;
END LOOP; /* end loop over each master */
-- DROP TABLE buffer_make2db_reunit_2d_topics;
my_reunited := substr(my_reunited, 2, length(my_reunited)); -- cut off first
ELSE IF my_line = 'DR' THEN
/*****************************/
FOR my_record IN
SELECT XrefDB.XrefDBName, EntryXrefDB.XrefPrimaryIdentifier, EntryXrefDB.XrefSecondaryIdentifier,
EntryXrefDB.XrefTertiaryIdentifier, EntryXrefDB.XrefOtherIdentifiers
FROM XrefDB, EntryXrefDB
WHERE CAST(EntryXrefDB.AC AS varchar(32)) = my_AC
AND EntryXrefDB.XrefDBCode = XrefDB.XrefDBCode AND activated IS TRUE
UNION
(SELECT XrefDBDynamic.XrefDBName, EntryXrefDBDynamic.XrefPrimaryIdentifier, EntryXrefDBDynamic.XrefSecondaryIdentifier,
EntryXrefDBDynamic.XrefTertiaryIdentifier, EntryXrefDBDynamic.XrefOtherIdentifiers
FROM XrefDBDynamic, EntryXrefDBDynamic
WHERE CAST(EntryXrefDBDynamic.AC AS varchar(32)) = my_AC
AND EntryXrefDBDynamic.XrefDBCode = XrefDBDynamic.XrefDBCode AND activated = 'true'
ORDER BY 1)
ORDER BY 1
LOOP
IF my_reunited IS NULL
THEN
my_reunited := my_record.XrefDBName || '; ' || CAST (my_record.XrefPrimaryIdentifier AS varchar(32));
ELSE
my_reunited := my_reunited || '
' || my_record.XrefDBName || '; '
|| CAST (my_record.XrefPrimaryIdentifier AS varchar(32));
END IF;
IF my_record.XrefSecondaryIdentifier IS NOT NULL
THEN
my_reunited := my_reunited || '; ' || CAST (my_record.XrefSecondaryIdentifier AS varchar(64));
END IF;
IF my_record.XrefTertiaryIdentifier IS NOT NULL
THEN
my_reunited := my_reunited || '; ' || CAST (my_record.XrefTertiaryIdentifier AS varchar(64));
END IF;
IF my_record.XrefOtherIdentifiers IS NOT NULL
THEN
my_reunited := my_reunited || '; ' || CAST (my_record.XrefOtherIdentifiers AS varchar(64));
END IF;
my_reunited := my_reunited || '.';
END LOOP;
-- This is the last line for a given entry
RAISE NOTICE '...entry % is processed', my_AC;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
IF my_reunited IS NULL OR my_reunited = '' THEN my_reunited := '--';
END IF;
RETURN my_reunited;
END;
FUNCTION TO CHECK THAT referenceLocationID ARE DIFFERENT FROM referenceType IN ReferenceLocation TABLES
-- FUNCTION TO CHECK THAT referenceLocationID ARE DIFFERENT FROM referenceType IN ReferenceLocation TABLES
----------------------------------------------------------------------------------------------------------
DECLARE
rl_id ALIAS for $1;
table_type ALIAS for $2;
my_type VARCHAR(32); -- ReferenceType.referenceType%TYPE; (produces a bug with postgreSQL 8.0 beta4!, even preceeded by core.)
BEGIN
SELECT INTO my_type ReferenceType.referenceType FROM ReferenceType, Reference
WHERE Reference.referenceID = rl_id AND Reference.referenceTypeID = ReferenceType.referenceTypeID
AND ReferenceType.referenceType = table_type;
IF NOT FOUND THEN
IF table_type = 'OTHER' THEN
-- any other defined category with no associated table and found in "OTHER" is also OK!
SELECT INTO my_type ReferenceType.referenceType FROM ReferenceType, Reference
WHERE Reference.referenceID = rl_id;
IF FOUND THEN RETURN 'true';
END IF;
END IF;
RETURN 'false';
END IF;
RETURN 'true';
END;
FUNCTION TO VERIFY THAT METHODS IN THE ARRAY OF MAPPING METHODS ARE LISTED IN MappingTopicDefinition Used as a CHECK constraint in SpotEntryMappingTopic
-- FUNCTION TO VERIFY THAT METHODS IN THE ARRAY OF MAPPING METHODS ARE LISTED IN MappingTopicDefinition
-------------------------------------------------------------------------------------------------------
-- Used as a CHECK constraint in SpotEntryMappingTopic
DECLARE
my_mappingTechniques ALIAS for $1;
my_ar_length INT2;
BEGIN
-- extract the length of the first dimension
my_ar_length :=
common.array_dim_length(CAST('{}' AS TEXT[]), CAST(my_mappingTechniques AS VARCHAR[]), CAST('{}' AS INTEGER[]), CAST('{}' AS FLOAT[]), 1);
IF (my_ar_length IS NULL OR my_ar_length < 1) THEN RETURN 'false';
END IF;
FOR ii IN 1..(my_ar_length)
LOOP
IF NOT EXISTS (SELECT * FROM MappingTopicDefinition WHERE mappingTechnique = my_mappingTechniques[ii])
THEN
RETURN 'false';
END IF;
END LOOP;
RETURN 'true';
END;
FUNCTION TO SUBSTITUTE A PATTERN BY ANOTHER ONE IN A TEXT
-- FUNCTION TO SUBSTITUTE A PATTERN BY ANOTHER ONE IN A TEXT
------------------------------------------------------------
DECLARE
my_pattern_1 ALIAS for $2;
my_pattern_2 ALIAS for $3;
my_old_text TEXT;
my_new_text TEXT;
BEGIN
my_old_text := $1;
IF strpos(my_old_text, my_pattern_1) = 0 THEN
RETURN my_old_text;
END IF;
my_new_text := '';
WHILE strpos(my_old_text, my_pattern_1) <> 0
LOOP
my_new_text := my_new_text || substr(my_old_text, 1, strpos(my_old_text, my_pattern_1) -1)
|| my_pattern_2;
my_old_text := substr(my_old_text, strpos(my_old_text, my_pattern_1) + length(my_pattern_1),
length(my_old_text));
END LOOP;
RETURN my_new_text || my_old_text;
END;
Generated by PostgreSQL Autodoc